﻿using System;
using System.Data;
using System.Windows.Forms;

namespace idataplatform
{
    public partial class loadhydata : Form
    {
        public loadhydata()
        {
            InitializeComponent();
        }

        private void loadhydata_Load(object sender, EventArgs e)
        {
            conncls conns = new conncls();
            string chgdateSQL = "SELECT DATE_FORMAT(MAX(jftime),'%Y年%c月%d日') FROM jfchange_ipark;";
            label3.Text = conns.varStr(chgdateSQL);
            string hxdateSQL = "SELECT DATE_FORMAT(MAX(HXTIME),'%Y年%c月%d日') FROM qhxtab_ipark;";
            label5.Text = conns.varStr(hxdateSQL);

            pubcls.upmark1 = false;
        }

        private void button1_Click(object sender, EventArgs e)
        {
            OpenFileDialog dialog = new OpenFileDialog();
            dialog.Title = "请选择文件夹";
            dialog.Filter = "excel07文件(*.xlsx)|*.xlsx|excel03文件(*.xls)|*.xls";
            if (dialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                string file = dialog.FileName;

                ImportExcel ie = new ImportExcel();

                DataTable dt = new DataTable();
                dt = ie.ExcelToTable(file);

                if (dt.Columns.Count == 11 && dt.Columns[0].ColumnName == "会员卡号")
                {
                    conncls conns = new conncls();
                    int i;
                    string upSql = "INSERT INTO jfchange_ipark(hyno,phoneno,jftime,jftype,jfint,operator,cttime,djtype,jfmark,lyid,lytype) VALUES ";
                    for (i = 0; i < dt.Rows.Count; i++)
                    {
                        upSql = upSql + "('" + dt.Rows[i][0].ToString() + "','" + dt.Rows[i][1].ToString() + "','" + dt.Rows[i][2].ToString() + "','" + dt.Rows[i][3].ToString() + "'," + dt.Rows[i][4].ToString() + ",'" + dt.Rows[i][5].ToString() + "','" + dt.Rows[i][6].ToString() + "','" + dt.Rows[i][7].ToString() + "','" + dt.Rows[i][8].ToString() + "','" + dt.Rows[i][9].ToString() + "','" + dt.Rows[i][10].ToString() + "'),";
                    }
                    upSql = upSql.Substring(0, upSql.Length - 1) + ";";
                    conns.sqlexec(upSql);
                    MessageBox.Show("积分变动数据上传完成！");
                    pubcls.upmark1 = true;
                    button1.Enabled = false;
                    string chgdateSQL = "SELECT DATE_FORMAT(MAX(jftime),'%Y年%c月%d日') FROM jfchange_ipark;";
                    label3.Text = conns.varStr(chgdateSQL);
                }
                else
                {
                    MessageBox.Show("选择的数据表不正确或格式不正确，请联系管理员。");
                }
            }

        }

        private void button2_Click(object sender, EventArgs e)
        {
            OpenFileDialog dialog = new OpenFileDialog();
            dialog.Title = "请选择文件夹";
            dialog.Filter = "excel07文件(*.xlsx)|*.xlsx|excel03文件(*.xls)|*.xls";
            if (dialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                string file = dialog.FileName;

                ImportExcel ie = new ImportExcel();

                DataTable dt = new DataTable();
                dt = ie.ExcelToTable(file);

                if (dt.Columns.Count == 11 && dt.Columns[0].ColumnName == "会员卡号")
                {
                    conncls conns = new conncls();
                    int i;
                    string upSql = "INSERT INTO qhxtab_ipark(qqd,qsx,qtype,jtqno,scqno,qname,qcb,qbt,qnos,qyxq,qusetime,qsyfw,phoneno,hyno,hyuid,qsysc,qhxf,qstatus1,tftype,fqyw,fqyename,fytime,qstatus2,hxyw,hxddno,hxtime,hxsc,hxdx,hxusers,yqdid,eqdid,sqdid,siqdid) VALUES ";
                    for (i = 0; i < dt.Rows.Count; i++)
                    {
                        upSql = upSql + "('" + dt.Rows[i][0].ToString() + "','" + dt.Rows[i][1].ToString() + "','" + dt.Rows[i][2].ToString() + "','" + dt.Rows[i][3].ToString() + "'," + dt.Rows[i][4].ToString() + ",'" + dt.Rows[i][5].ToString() + "','" + dt.Rows[i][6].ToString() + "','" + dt.Rows[i][7].ToString() + "','" + dt.Rows[i][8].ToString() + "','" + dt.Rows[i][9].ToString() + "','" + dt.Rows[i][10].ToString() + "'),";
                    }
                    upSql = upSql.Substring(0, upSql.Length - 1) + ";";
                    conns.sqlexec(upSql);
                    MessageBox.Show("券核销数据上传完成！");
                    pubcls.upmark1 = true;
                    button2.Enabled = false;
                    string hxdateSQL = "SELECT DATE_FORMAT(MAX(HXTIME),'%Y年%c月%d日') FROM qhxtab_ipark;";
                    label5.Text = conns.varStr(hxdateSQL);
                }
                else
                {
                    MessageBox.Show("选择的数据表不正确或格式不正确，请联系管理员。");
                }
            }
        }
    }
}
